#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin", "$FindBin::Bin");
use db_parameters;
use ensembl_parameters;
use chromosomes;
use min_max;

use DBI;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_prot_exons

	Description:
		Obtain the loci of exons associated with each ENSEMBL gene
	Populates DB tables:
		exon_loci
		prot_exons

888888888888888888888888888888888888888888888888888888888888888888888
HEADLINE


##########################################################################################

#	retrieve_ensembl_loci

#		retrieve data from ENSEMBL

##########################################################################################
sub retrieve_ensembl_exon_counts($$\%)
{
	my ($dbh, $taxon, $exon_loci) = @_;
	my %prot_ids;
	my $ens_db_name = $taxon->[ENS_TAXON] .'_core_'.$taxon->[ENS_VERSION];
	print STDERR "\t\tConnecting to dbi:mysql:$ens_db_name:$ens_db_host $ens_db_user\n";
	my $mysql_dbh = DBI->connect("dbi:mysql:$ens_db_name:$ens_db_host", "$ens_db_user", '',
							{
								RaiseError => 0,
								AutoCommit => 1,
								PrintError => 0
							}
						)
	or die "Database connection not made: DBI::errstr()";
	my $array = $mysql_dbh->selectall_arrayref(<<'PL/SQLCMD');
    SELECT
        ensp.stable_id as ensp,
        exon_stable_id.stable_id,
        ex.seq_region_start,
        ex.seq_region_end
    FROM
        exon ex INNER JOIN
        exon_stable_id USING (exon_id) INNER JOIN
        exon_transcript USING (exon_id) INNER JOIN
        transcript t USING (transcript_id) INNER JOIN
        translation p ON (t.transcript_id = p.transcript_id) INNER JOIN
        translation_stable_id ensp USING(translation_id) INNER JOIN
        gene_stable_id gs ON (gs.gene_id = t.gene_id)
PL/SQLCMD
	#
	#	taxon.exon_loci
	#
	print STDERR "\t\tSaving exon data for $taxon->[TAXON] to panda...\n";
	$dbh->do("COPY taxon.prot_exons".
				"(prot_id, exon_id, start, finish) FROM STDIN");

	for my $data(@$array)
	{
		my ($prot_id,  $exon_id,  $start, $finish) = @$data;
		$dbh->func("$prot_id\t$exon_id\t$start\t$finish\n", 'putline');
		$exon_loci->{$exon_id} = [$start, $finish];
		$prot_ids{$prot_id}++;
	}
	$dbh->func("\\.\n", 'putline');
	$dbh->func('endcopy');
	$mysql_dbh->disconnect;
	return scalar keys %prot_ids;
}





#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888

# Main logic

#88888888888888888888888888888888888888888888888888888888888888888888888888888888888888888


#
#	Get DB parameters
#
my @db_param = get_ensembl_taxon_data();
my $dbh = connect_to_panda();

use constant EXON_LOCUS_BEG =>0;
use constant EXON_LOCUS_END =>1;

$dbh->do("TRUNCATE taxon.prot_exons");
$dbh->do("VACUUM FULL taxon.prot_exons;");
my (%protid_to_exonid, %protid_to_geneid, %exon_loci);
for my $taxon_data(@db_param)
{
	my $taxon = $taxon_data->[TAXON];
	print STDERR "\tExon counts for ", $taxon, ":\n";
	#
	#	Retrieve from ENSEMBL
	#
	print STDERR "\t\tRetrieving from Ensembl...\n";
	my $cnt = retrieve_ensembl_exon_counts($dbh, $taxon_data, %exon_loci);
	print STDERR "\t\texon data for $cnt proteins\n";
}
print STDERR "\t", scalar keys %exon_loci, " exons retrieved from ENSEMBL altogether...\n";
$dbh->do("ANALYSE taxon.prot_exons;");


print STDERR "\tCompleted\n";
$dbh->disconnect();
